﻿-- Script n°008: "authorsnote" table.

BEGIN;

-- Drop objects if any
DROP TABLE IF EXISTS authorsnote;
DROP SEQUENCE IF EXISTS seq_authorsnote;

-- Create sequence
CREATE SEQUENCE seq_authorsnote
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_authorsnote OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_authorsnote TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_authorsnote TO cheliouwebdev;

-- Create table
CREATE TABLE authorsnote (
   id                   NUMERIC              NOT NULL DEFAULT nextval('seq_authorsnote'::regclass),
   photoid				NUMERIC              NOT NULL,
   locale               VARCHAR(100)         NOT NULL,
   note                 TEXT                 NOT NULL,
   CONSTRAINT pk_authorsnote PRIMARY KEY (id),
   CONSTRAINT ak_unique_authorsnote_language UNIQUE (photoid, locale)
) WITH (
  OIDS=FALSE
);

-- set table ownership
ALTER TABLE authorsnote
   ADD CONSTRAINT fk_authorsnote_photo FOREIGN KEY (photoid)
      REFERENCES photo (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE authorsnote OWNER TO chelioudbdev;
GRANT ALL ON TABLE authorsnote TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE authorsnote TO cheliouwebdev;

COMMIT;
